import pymysql
#
#
# db = pymysql.connect(
#     host= 'localhost',
#     port= 3306,
#     database= 'hrjt',
#     user= 'root',
#     password= '123456',
#     charset='utf8'
# )
#
# cursor = db.cursor()
#
# username_list = ["Jack","Bob","Alice","Helen","Tom","Lancy","Taylor","Swift","Medy","Lily"]
# age_list = [10,11,12,17,8,9,15,16,12,12]
# sex_list = ["male","woman","male","woman","male","woman","male","woman","male","woman"]
# high_list = [1.78,1.55,1.77,1.58,1.68,1.64,1.88,1.7,1.79,1.58]
# for i in range(10):
#     value = (f'("{username_list[i]}",{age_list[i]},"{sex_list[i]}",{high_list[i]})')
#     sql = f"insert into person values {value}"
#     cursor.execute(sql)
#
# # sql = f"insert into person values('Jack',10,'male',1.78)"
# # cursor.execute(sql)
#
#
# db.commit()
# cursor.close()
# db.close()
#


# 查询语句
# db = pymysql.connect(
# #     host= 'localhost',
# #     port=3306,
# #     database= "hrjt",
# #     user= 'root',
# #     password="123456",
# #     charset="utf8"
# # )
# #
# # with db.cursor() as cursor:
# #     sql = "select * from person;"
# #     cursor.execute(sql)
# #     data = cursor.fetchmany(3)
# #     print(data)
# #     db.commit()
# #
# # db.close()

# 插入操作
# db = pymysql.connect(
#     host= "localhost",
#     port=3306,
#     database="hrjt",
#     user="root",
#     password="123456",
#     charset="utf8"
# )
# value = ("('Davin',15,'Man',1.75),"
#          "('Eric',10,'Man',1.60)")
# with db.cursor() as cursor:
#
#     sql = "insert into person value {};".format(value)
#     cursor.execute(sql)
#     db.commit()
# db.close()


# 修改操作
# db = pymysql.connect(
#     host= "localhost",
#     port=3306,
#     database="hrjt",
#     user="root",
#     password="123456",
#     charset="utf8"
#
# )
#
# with db.cursor() as cursor:
#     sql = "update person set sex = 'Man' where username = 'Jack';"
#     cursor.execute(sql)
#     db.commit()
# db.close()

# 删除操作
# db = pymysql.connect(
#     host= "localhost",
#     port=3306,
#     database="hrjt",
#     user="root",
#     password="123456",
#     charset="utf8"
# )
#
# with db.cursor() as cursor:
#     sql = "delete from person where username = 'Jack';"
#     cursor.execute(sql)
#     db.commit()
# db.close()

import DButils

def test_insert():
    sql = "insert into person values(%s,%s,%s,%s);"
    param = ("张三",14,"male",1.75)
    DButils.manipulate(sql,param,"localhost",3306,"hrjt","root","123456","utf8")
    print("插入操作成功")
def test_delete():
    sql = "delete from person where username = %s;"
    param = ("张三",)
    DButils.manipulate(sql, param, "localhost", 3306, "hrjt", "root", "123456", "utf8")
    print("删除成功")

def test_update():
    sql = "update person set high = %s where username = %s;"
    param  = ("1.88","Melon")
    DButils.manipulate(sql, param, "localhost", 3306, "hrjt", "root", "123456", "utf8")
    print("更新操作成功")

def test_select():
    sql = "select * from person where age between %s and %s;"
    param = ('12','15')
    results = DButils.select(sql, param, "localhost", 3306, "hrjt", "root", "123456", "utf8")
    for i in results:
        print(i)
    print("查询成功")


if __name__ == '__main__':
    test_select()